Attribute VB_Name = "CDB_TF_3_3"
Sub Export_QE_CDB_3_3()


'Q-EXACTIVE TRACEFINDER CDB EXPORTER
'This macro exports compound names and scan events from an existing method summary Excel sheet and saves results in
'a .csv file for import into a TraceFinder Compound Database.



'VARIABLES

Dim End_time
Dim RT
Dim Start_time
Dim Window


'INTEGERS

Dim First_DataRow As Integer
Dim DataRow As Integer
Dim ExportRow As Integer


'REFERENCES

Dim DataBook As Workbook
Dim DataSheet As Worksheet
Dim DataTable As ListObject

Dim ExportBook As Workbook
Dim ExportSheet As Worksheet

Dim NameCell As Range


'STRINGS

Dim Adduct As String
Dim File_name As String




'SET INITIAL VARIABLES

Application.ScreenUpdating = False  'TURN OFF SCREEN UPDATING TO INCREASE SPEED

'First_DataRow = 8  '<--------------- FIRST ROW IN SUMMARY SCAN EVENT TABLE



'SET REFERENCES

Set DataBook = ThisWorkbook
Set DataSheet = ThisWorkbook.Worksheets(1)
Set DataTable = DataSheet.ListObjects("Table4")


'REMIND USER WHAT THIS MACRO DOES

MsgBox "A .csv file that can be imported into a TraceFinder 3.3" & vbLf & "Compound Database will be created!" _
& vbLf & vbLf & "NOTE:" & vbLf & vbLf & "The TF 3.3 CDB only uses a single response threshold (enter this in the MS Threshold (Area) column).", vbInformation, "TF 3.3 CDB Export" _



'GET SAVE PATH FROM USER

ChDrive "C"
On Error Resume Next
ChDir "C:\Xcalibur\Methods\"
On Error GoTo 0

File_name = Application.GetSaveAsFilename("TF_3_3_CDB_" & Format(Now(), "mmddyyhhmm") & ".csv", "Comma Separated Files (*.csv),*.csv", 0, "Save Sequence .csv File")
If File_name = "False" Then Exit Sub  'EXIT MACRO WITHOUT SAVING IF USER SELECTS CANCEL



'CREATE A NEW WORKBOOK AND SET StdCheckSheet REFERENCE

Application.DisplayAlerts = False
Set ExportBook = Workbooks.Add
    With ExportBook
        .Worksheets(1).Name = "TraceFinder_CDB_" & Format(Now(), "mmddyyhhmm")
        .SaveAs Filename:=File_name, FileFormat:=xlCSV
    End With
Application.DisplayAlerts = True


Set ExportSheet = ExportBook.Worksheets(1)


'ENTER CSV SHEET HEADERS


ExportSheet.Cells(1, 1) = "TraceFinder Compound Database Export"
ExportSheet.Cells(1, 2) = "Schema Version 1"

ExportSheet.Cells(2, 9) = "PEAK 1"
'ExportSheet.Cells(2, 18) = "Peak2"

ExportSheet.Cells(3, 1) = "CompoundName"
ExportSheet.Cells(3, 2) = "ExperimentType"
ExportSheet.Cells(3, 3) = "ChemicalFormula"
ExportSheet.Cells(3, 4) = "Category"
ExportSheet.Cells(3, 5) = "CAS"
ExportSheet.Cells(3, 6) = "Ionization"
ExportSheet.Cells(3, 7) = "ResponseThreshold"
ExportSheet.Cells(3, 8) = "ExtractedMass"   '<--- Peak1
ExportSheet.Cells(3, 9) = "Adduct"
ExportSheet.Cells(3, 10) = "Polarity"
ExportSheet.Cells(3, 11) = "ChargeState"
ExportSheet.Cells(3, 12) = "RT"
ExportSheet.Cells(3, 13) = "Window"
ExportSheet.Cells(3, 14) = "CollisionEnergy"
ExportSheet.Cells(3, 15) = "Lens"
ExportSheet.Cells(3, 16) = "EnergyRamp"
ExportSheet.Cells(3, 17) = "Fragment"
ExportSheet.Cells(3, 18) = "Fragment"
ExportSheet.Cells(3, 19) = "Fragment"
ExportSheet.Cells(3, 20) = "Fragment"
ExportSheet.Cells(3, 21) = "Fragment"



'COPY COMPOUND DATA TO CSV SHEET

ExportRow = 4


'SKIP IF ROW NOT VISIBLE

For Each NameCell In DataTable.ListColumns("Compound").DataBodyRange.SpecialCells(xlCellTypeVisible)
DataRow = NameCell.Row - 6


'COPY COMPOUND DATA FROM EXCEL WORKSHEET

ExportSheet.Cells(ExportRow, 1) = DataTable.ListColumns("Compound").Range(DataRow)             'COMPOUND NAME
ExportSheet.Cells(ExportRow, 2) = "XIC"                                                        'EXPERIMENT TYPE
ExportSheet.Cells(ExportRow, 3) = DataTable.ListColumns("Formula").Range(DataRow)              'FORMULA
ExportSheet.Cells(ExportRow, 4) = DataTable.ListColumns("Standard Mix").Range(DataRow)         'CATEGORY
ExportSheet.Cells(ExportRow, 5) = DataTable.ListColumns("CAS").Range(DataRow)                  'CAS
ExportSheet.Cells(ExportRow, 6) = "ESI"                                                        'COPY IONIZATION METHOD
ExportSheet.Cells(ExportRow, 7) = DataTable.ListColumns("MS Threshold (Area)").Range(DataRow)  'AREA THRESHOLD

'PEAK 1

ExportSheet.Cells(ExportRow, 8) = DataTable.ListColumns("Adduct m/z").Range(DataRow)           'EXTRACTED M/Z (PRECURSOR)
ExportSheet.Cells(ExportRow, 9) = Adduct                                                       'ADDUCT


ExportSheet.Cells(ExportRow, 10) = "+"                                                         'POLARITY
ExportSheet.Cells(ExportRow, 11) = "1"                                                         'CHARGE STATE
ExportSheet.Cells(ExportRow, 12) = DataTable.ListColumns("RT").Range(DataRow)                  'RT
ExportSheet.Cells(ExportRow, 13) = DataTable.ListColumns("RT Window").Range(DataRow) * 60      'RT WINDOW (SECONDS)
ExportSheet.Cells(ExportRow, 14) = DataTable.ListColumns("NCE").Range(DataRow)                 'COLLISION ENERGY
ExportSheet.Cells(ExportRow, 15) = ""                                                          'LENS
ExportSheet.Cells(ExportRow, 16) = 0                                                           'ENERGY RAMP
ExportSheet.Cells(ExportRow, 17) = DataTable.ListColumns("Fragment 1").Range(DataRow)
ExportSheet.Cells(ExportRow, 18) = DataTable.ListColumns("Fragment 2").Range(DataRow)
ExportSheet.Cells(ExportRow, 19) = DataTable.ListColumns("Fragment 3").Range(DataRow)
ExportSheet.Cells(ExportRow, 20) = DataTable.ListColumns("Fragment 4").Range(DataRow)
ExportSheet.Cells(ExportRow, 21) = DataTable.ListColumns("Fragment 5").Range(DataRow)


ExportRow = ExportRow + 1

Next NameCell



'SAVE AND CLOSE .CSV FILE

ExportBook.Save
ExportBook.Close Savechanges = False

MsgBox "TF 3.3 CDB export file saved as " & File_name, vbInformation




End Sub

